import pymysql
from time import sleep
import pandas as pd
from pandas import DataFrame as df
import requests
from lxml import etree
import random
class human_detail:
    host='192.168.0.113'
    port=3306
    user='coname'
    passwd='xZS6p4LDcKCwn4Yb'
    charset='utf8'
    db='co'
    def __init__(self):#初始化读取excel
        self.wb=pd.read_excel(r'D:\wky\spider\humansource\humansource_detail\human1.xlsx',sheet_name="Sheet1")
        self.nums=self.wb.shape[0]
        self.header=[{"User-Agent":"Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_8; en-us) AppleWebKit/534.50 (KHTML, like Gecko) Version/5.1 Safari/534.50"},
                    {"User-Agent":"Mozilla/5.0 (Windows; U; Windows NT 6.1; en-us) AppleWebKit/534.50 (KHTML, like Gecko) Version/5.1 Safari/534.50"},
                    {"User-Agent":"Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0;"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"},
                    {"User-Agent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv"},
                    {"User-Agent":"Mozilla/5.0 (Windows NT 6.1; rv"},
                    {"User-Agent":"Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; en) Presto/2.8.131 Version/11.11"},
                    {"User-Agent":"Opera/9.80 (Windows NT 6.1; U; en) Presto/2.8.131 Version/11.11"},
                    {"User-Agent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_0) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Maxthon 2.0)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; TencentTraveler 4.0)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; The World)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; SE 2.X MetaSr 1.0; SE 2.X MetaSr 1.0; .NET CLR 2.0.50727; SE 2.X MetaSr 1.0)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 360SE)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Avant Browser)"},
                    {"User-Agent":"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)"},]

    def read(self):#获取excel的数据
        for i in range(1,self.nums,1):
            fileurl=self.wb.loc[i,'fileurl']
            try:
                txt =etree.HTML(requests.get(fileurl,headers=random.choice(self.header),verify=False).text)
                cont = txt.xpath("//div[@class='article']")
                contet =cont[0].xpath('string(.)')
                self.wb.loc[i,'content']=contet
                print("存入第"+str(i)+"个")
            except Exception as e:
                print("存入第"+str(i)+"个异常")
                continue
            # self.save_mysql(self.wb.loc[i,'content'],fileurl)
            sleep(4)
        self.save_mysql()
    
    def open_datas(self):#打开数据库
        try:
            self.conn=pymysql.Connection(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
            self.cursor=self.conn.cursor()
            print("打开数据库成功!")
            return self.conn,self.cursor
        except Exception as e:
            print("打开数据库失败>>>",e)

            
    def close_datas(self,conn,cursor):#关闭数据库
        try:
            cursor.close()
            conn.close()
            print("关闭数据成功!")
        except Exception as e:
            print("关闭数据库失败>>>",e)

    def save_mysql(self):#保存数据
        df(self.wb).to_excel(excel_writer=r'D:\wky\spider\humansource\humansource_detail\human2.xlsx',sheet_name='Sheet1',header=True,index=False,encoding='utf-8')


        # conn,cursor=self.open_datas()
        #插入数据
        # try:   
        #     if not cursor.execute('select srname from co_human_source where srname="{}"'.format(name)):
        #         cursor.execute('insert into co_human_source (keywordid,srname,fileurl) values("{}","{}","{}")'.format(id,name,url))
        #         conn.commit()
        #         print('录入到数据库成功!')
        #     else:
        #         pass
        # except Exception as e:
        #     print("录入到数据失败>>>",e)
        #更改数据
        # try:
        #     cursor.execute('update co_human_source set content = "{}" where fileurl="{}"'.format(content,url))
        #     conn.commit()
        #     print("改变数据成功!")
        # except Exception as e:
        #     print("改变数据失败>>>",e)

        # self.close_datas(conn,cursor)

    def run(self):#运行
        self.read()

if __name__ == "__main__":
    hd=human_detail()
    hd.run()
    



        


